<%@Page Language="VB"%>

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>
<%@Import Namespace="System.Data.Common" %>

<%@ Register TagPrefix="wrox" TagName="connect" Src="..\global\connect-strings.ascx" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>Using Default and Specific Table and Column Mappings</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Using Default and Specific Table and Column Mappings</span><hr />
<!--------------------------------------------------------------------------->

<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server"/>

<div>Connection string: <b><span id="outConnect" runat="server"></span></b></div>
<div>Books SELECT command: <b><span id="outSelectBooks" runat="server"></span></b></div>
<div>Authors SELECT command: <b><span id="outSelectAuthors" runat="server"></span></b></div>
<div id="outError" runat="server">&nbsp;</div>

<b>DataSet.Tables Collection</b>
<asp:datagrid id="dgrTables" runat="server" /><br />
<b>DataSet.Relations Collection</b>
<asp:datagrid id="dgrRelations" runat="server" /><br />
<b>Contents of DataSet.Tables(0)</b>
<asp:datagrid id="dgrBooksData" runat="server" /><br />
<b>Contents of DataSet.Tables(1)</b>
<asp:datagrid id="dgrAuthorsData" runat="server" /><br />

<script language="vb" runat="server">

Sub Page_Load()

   'get connection string from ..\global\connect-strings.ascx user control
   Dim strConnect = ctlConnectStrings.OLEDBConnectionString
   outConnect.innerText = strConnect 'and display it

   'specify the SELECT statement to extract the BookList data
   Dim strSelectBooks As String
   strSelectBooks = "SELECT * FROM BookList WHERE ISBN LIKE '18610033%'"
   outSelectBooks.innerText = strSelectBooks   'and display it

   'specify the SELECT statement to extract the BookAuthor data
   Dim strSelectAuthors As String
   strSelectAuthors = "SELECT * FROM BookAuthors WHERE ISBN LIKE '18610033%'"
   outSelectAuthors.innerText = strSelectAuthors   'and display it

   'create a new Connection object using the connection string
   Dim objConnect As New OleDbConnection(strConnect)

   'create a new Command object
   Dim objCommand As New OleDbCommand()

   'set the properties
   objCommand.Connection = objConnect
   objCommand.CommandType = CommandType.Text
   objCommand.CommandText = strSelectBooks

   'create a new DataAdapter object
   Dim objDataAdapter As New OleDbDataAdapter
   'and assign the Command object to it
   objDataAdapter.SelectCommand = objCommand

   'declare a variable to hold a DataTableMapping object
   Dim objTableMapping As DataTableMapping

   'add the default table mapping "Table" - this table name will be used
   'if you don't provide a table name when filling the DataSet
   objTableMapping = objDataAdapter.TableMappings.Add("Table", "DefaultBookList")

   'now add the column mappings for this table
   With objTableMapping.ColumnMappings
      .Add("ISBN", "BookCode")
      .Add("Title", "BookTitle")
      .Add("PublicationDate", "Published")
   End With

   'add a table mapping so that data from the table named "BookAuthors" in
   'the database will be placed in a table named "AuthorList"
   objTableMapping = objDataAdapter.TableMappings.Add("BookAuthors", "AuthorList")

   'add the column mappings for this table
   With objTableMapping.ColumnMappings
      .Add("ISBN", "BookCode")
      .Add("FirstName", "Forename")
      .Add("LastName", "Surname")
   End With

   'new we're ready to fill a DataSet object from the database

   'create a new DataSet object
   Dim objDataSet As New DataSet()

   Try

      'get the data from the "BookList" table in the database and
      'put it into a the default table in the DataSet object
      objDataAdapter.Fill(objDataSet)

      'change the SELECT statement in the Command object
      objCommand.CommandText = strSelectAuthors
      'then get data from "BookAuthors" table in the database
      'and put it into the mapped table in the DataSet
      objDataAdapter.Fill(objDataSet, "BookAuthors")

   Catch objError As Exception

      'display error details
      outError.innerHTML = "<b>* Error while accesing data</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

   'declare a variable to hold a DataRelation object
   Dim objRelation As DataRelation

   'create a Relation object to link the two tables
   'note that it uses the new mapped table and column names
   objRelation = New DataRelation("BookAuthors", _
                     objDataSet.Tables("DefaultBookList").Columns("BookCode"), _
                     objDataSet.Tables("AuthorList").Columns("BookCode"))
   'and add it to the DataSet object's Relations collection
   objDataSet.Relations.Add(objRelation)

   'now we're ready to display the contents of the DataSet object
   'bind the collection of Tables to the first DataGrid on the page
   dgrTables.DataSource = objDataSet.Tables
   dgrTables.DataBind()

   'bind the collection of Relations to the second DataGrid on the page
   dgrRelations.DataSource = objDataSet.Relations
   dgrRelations.DataBind()

   'create a DataView object to use with the tables in the DataSet
   Dim objDataView As New DataView

   'get the default view of the default table into the DataView object
   objDataView = objDataSet.Tables("DefaultBookList").DefaultView
   'and bind it to the third DataGrid on the page
   dgrBooksData.DataSource = objDataView
   dgrBooksData.DataBind()

   'then do the same for the mapped BookAuthors table
   objDataView = objDataSet.Tables("AuthorList").DefaultView
   dgrAuthorsData.DataSource = objDataView
   dgrAuthorsData.DataBind()

End Sub
</script>


<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
